CREATE DATABASE Versus 
GO

Use Versus
--DROP TABLE Message
--DROP TABLE Account
--DROP TABLE AccountLog
--DROP TABLE AccountStatistic
--DROP TABLE GameLog
--DROP TABLE GameList
CREATE TABLE Message
(
	MessageID int identity(1,1) primary key,
	Sender nchar(20) NOT NULL,
	Recipient nchar(20) NOT NULL,
	Contents nchar(300) NOT NULL,
	Status tinyint NOT NULL
)

INSERT INTO Message(Sender, Recipient, Contents, Status) VALUES('Lynk','NgocUT','ii tenki desu ne','1')
INSERT INTO Message(Sender, Recipient, Contents, Status) VALUES('LongBM','NgocUT','Hello world','0')
--------------------------------------------------------------------------------------------
CREATE TABLE GameLog
(
	GameID int primary key identity(1,1),
	Player1 nchar(20) NOT NULL,
	Player2 nchar(20)NOT NULL,
	BeginTime datetime NOT NULL,
	EndTime datetime NOT NULL,
	Result int NOT NULL,
	Description nchar(100)
)

INSERT INTO GameLog(Player1,Player2,BeginTime,EndTime,Result,Description) VALUES('Lynk','LongBM','11:15','11:50','1','Lynk wins')
INSERT INTO GameLog(Player1,Player2,BeginTime,EndTime,Result,Description) VALUES('Lynk','VietPQ','8:30','8:50','0','Lynk loses')

--------------------------------------------------------------------------------------------
CREATE TABLE GameList
(
	GameNo int primary key identity(1,1),
	GameName nchar(20) NOT NULL
)

INSERT INTO GameList(GameName) VALUES('Chinese Chess')
INSERT INTO GameList(GameName) VALUES('King Chess') 
INSERT INTO GameList(GameName) VALUES('Gomoku')
INSERT INTO GameList(GameName) VALUES('Othelo')

--------------------------------------------------------------------------------------------
CREATE TABLE Account
(
	Username nchar(20) primary key,
	Password nchar(20) NOT NULL,
	Email nchar(50),
	Avatar nchar(20)
)

INSERT INTO Account VALUES('Lynk','123456','xyz','')
INSERT INTO Account VALUES('LongBM','123456','abc','')
INSERT INTO Account VALUES('VietPQ','123456','pqr','')
INSERT INTO Account VALUES('NgocUT','123456','hik','')

--------------------------------------------------------------------------------------------
CREATE TABLE AccountLog
(
	LogID int identity(1,1) primary key,
	Username nchar(20),
	SignInTime datetime,
	SignOutTime datetime
)

INSERT INTO AccountLog(Username, SignInTime, SignOutTime) VALUES('NgocUT',getdate(),getdate())
INSERT INTO AccountLog(Username, SignInTime, SignOutTime) VALUES('LongBM',getdate(),getdate())

--------------------------------------------------------------------------------------------
CREATE TABLE AccountStatistic
(
	Username nchar(20),
	GameNo int,
	Win int,
	Lose int, 
	Draw int,
	primary key (Username, GameNo)
)

INSERT INTO AccountStatistic VALUES('LongBM','1','3','1','2')
INSERT INTO AccountStatistic VALUES('VietPQ','2','8','3','12')
INSERT INTO AccountStatistic VALUES('VietPQ','3','5','3','12')

--------------------------------------------------------------------------------------------
CREATE TABLE BugReport
(
	ID int primary key identity(1,1),
	Username nchar(20) NOT NULL,
	Report nchar(500)
)

INSERT INTO BugReport(Username,Report) VALUES('Quangviet','Game crashed')
